package com.scgri.utils;

import com.scgri.config.Const;

import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.*;
import java.util.*;

public class JDBCUtil {
    private static Properties p = null;

    static {
        try {
            URL location = Const.class.getProtectionDomain().getCodeSource()
                    .getLocation();
            String filePath = URLDecoder.decode(location.getPath(), "utf-8");
            if (filePath.endsWith(".jar")){
                filePath = filePath.substring(0, filePath.lastIndexOf("/") + 1);
            }
            java.io.File file = new java.io.File(filePath);
            filePath = file.getAbsolutePath()+"\\config\\db.properties";
            p = new Properties();
            InputStream resourceAsStream = new FileInputStream(filePath);
            p.load(resourceAsStream);
            Class.forName(p.getProperty("driverClass"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //封装获取连接对象
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"), p.getProperty("pwd"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    //封装释放资源
    public static void release(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Map> executeQuery(String sql) {
        try {
            /**
             * 查询
             */
            Connection conn = JDBCUtil.getConnection();

            PreparedStatement pst = conn.prepareStatement(sql);
            /*//准备SQL的参数赋值
            for (int i = 1; i <= obj.length; i++) {
                pst.setObject(i, obj[i-1]);
            }*/

            //执行SQL
            ResultSet rs = pst.executeQuery();

            //移动光标，如果有数据就返回true，没有就返回false
            List<Map> list = new ArrayList<>();
            while (rs.next()) {

                //rs转换map
                ResultSetMetaData rsmd = rs.getMetaData();
                //获取结果集的列数
                int count = rsmd.getColumnCount();
                //存储的是一行数据
                HashMap<String, Object> map = new HashMap<>();

                //遍历
                for (int i = 1; i <= count; i++) {
                    //字段名字
                    String name = rsmd.getColumnLabel(i);
                    //根据字段名从结果集中获取对应的数据
                    Object value = rs.getObject(name);
                    map.put(name, value);
                }
                /*//将map中的数据封装到对象中
                BeanUtils.populate(t, map);
                //将每行数据添加到集合中
                *//*list.add(t);*/
                list.add(map);
            }
            //释放资源
            JDBCUtil.release(rs, pst, conn);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
